require 'open-uri'
require 'taggable'

class Page < ActiveRecord::Base

    acts_as_taggable

    has_many :annorations

    ###############################################
    # virtual attribute for the thumbnail uri.
    def to_thumbnail_uri
        return Page.cache_thumbnail(self.to_uri)
    end
    
    ###############################################
    # pull down the thumbnail
    def self.cache_thumbnail(uri)

        # get the host from the uri
        host = URI.parse(uri).host
        
        # encode it to a file name
        file_name = host.gsub(/[^a-z]/, '_') + '.jpg'
        
        # build a full path to where the thumbnail would actually exist on disk
        full_path = File.join(RAILS_ROOT, "public", "thumbnails", file_name)

        # see if the file already exists
        file_name = get_thumbnail(host, file_name, full_path) if !File.exists?(full_path)
        
        # return the relative url to the thumbnail
        return '/thumbnails/' + file_name
    end
    
    ####################################
    # Update the user tags for the page
    def update_user_tags(user_id, tags)
    
        # get the tgs previously applied by this user to the page
        page = Page.find_by_uri(self.uri)
        old_tags = page ? User.find(user_id).tag_list_for_page(page.id) : Array.new
        
        # tag the page with the tags newly specified by the user 
        tag(tags, :attributes => {:user_id => user_id})
        
        # go through the old tags
        old_tags.each do |tag|
        
            # if the new tags don't include one of the old tags, remove it 
            if !tags.include?(tag.name)
                self.tag_remove(tag.name)
            end
        end
    end
    
    ####################################
    # Gets the path to an alexa thumbnail for a url
    def self.get_thumbnail host, file_name, full_path

        # our secret info is stored in aws.rb
        access_key_id = Account::AccountConfiguration.config(:aws_access_id)
        secret_id = Account::AccountConfiguration.config(:aws_secret_id)

        # set up some params for aws
        timestamp = Time.now.utc.strftime("%Y-%m-%dT%H:%M:%S.000Z")

        # the action is Thumbnail
        action = 'Thumbnail'

        # encode the signature
        signature = Base64.encode64(OpenSSL::HMAC::digest(OpenSSL::Digest::Digest.new('SHA1'), secret_id, action + timestamp)).strip

        # build the aws url        
        uri = URI.parse(

        "http://ast.amazonaws.com/?" +
        {
            "AWSAccessKeyId"  => access_key_id,
            "Timestamp"       => timestamp,
            "Signature"       => signature,
            "Action"          => action,
            "Url"             => host,
          }.to_a.collect{|item| item.first + "=" + CGI::escape(item.last.to_s) }.join("&")     # Put key value pairs into http GET format
        )
       
        # request the xml describing the thumbnails we want
        doc = open(uri).read

        begin
            # scrape the image url out of the xml
            m = doc.match(/\<aws:thumbnail[^\>]+exists=\"true\"\>(.+?)\<\//i)
        
            # we found an image url
            if m && m[1]

                # unencode the url
                thumb_url = m[1]
                thumb_url.gsub!(/\&amp;/, '&')

                # download the thumbnail
                File.open(full_path, "wb") { |f| f.write open(thumb_url).read }
                
                return file_name
                
            # see if it told us no thumbnail exists
            elsif m && m.match(/exists=\"false\"/)
                puts "No thumbnail for #{host}"
            else
                return "not_available.jpg"
            end    
            
        rescue
          puts "Could not access AWS"
        end
    end
    
    #########################################################
    # Gets the unique tags for a page
    # The acts_as_taggable methods won't work because multiple 
    # users can tag the same page with the same tag
    # 
    def self.popular_tags(page_id, options = {})
        sql = "SELECT DISTINCT ON(lower(tags.name), tags.id) * "
        sql << "FROM tags "
        sql << "INNER JOIN pages_tags ON tags.id = pages_tags.tag_id "
        sql << "WHERE pages_tags.page_id = ? "
        sql << "ORDER BY lower(tags.name), tags.id"
        add_limit!(sql, options)
        Tag.find_by_sql([sql, page_id])
    end
    
    #########################################################
    # Updates the statistics for a page
    def update_stats
        self.total_ratings = connection.select_value("SELECT COUNT(*) AS average_rating FROM annorations WHERE page_id = #{self.id}")
        self.total_comments = self.total_ratings 
        self.average_rating = connection.select_value("SELECT ROUND(AVG(rating*2)) AS average_rating FROM annorations WHERE page_id = #{self.id}")
        self.save!
    end
    
    #########################################################
    # Retrieves the top n number of ratings and ranks pages according to the average rating
    # Need to modify to use a digg algorithm 
    def self.top_rated_pages(max_pages = 5, ratings_to_consider = 100)
        sql = "SELECT a.id, a.a_page_id, a.total_ratings, a.average_rating, a.title, a.page_uri as uri, a.login, a.comment, a.updated_at "
        sql << "FROM ( "
        sql << "SELECT pages.total_ratings, pages.average_rating, pages.uri as page_uri, annorations.page_id as a_page_id, annorations.title, "
        sql << "pages.uri, annorations.rating, annorations.page_id, users.login, first_annoration.comment, pages.id, first_annoration.updated_at " 
        sql << "FROM annorations " 
        sql << "INNER JOIN pages ON annorations.page_id = pages.id " 
        sql << "INNER JOIN annorations as first_annoration ON pages.first_annoration_id = first_annoration.id "
        sql << "INNER JOIN users ON first_annoration.user_id = users.id "
        sql << "ORDER BY annorations.updated_at DESC "
        sql << "LIMIT #{ratings_to_consider} "
        sql << ") AS a "
        sql << "GROUP BY a.a_page_id, a.title, a.page_uri, a.page_uri, a.login, a.comment, a.id, a.updated_at, a.total_ratings, a.average_rating "
        sql << "ORDER BY average_rating DESC "
        sql << "LIMIT #{max_pages}"
        Page.find_by_sql(sql)
    end
    
    #########################################################
    # Retrieves the most recently newly annorated pages
    def self.recent_pages(max_pages = 5)
        # this needs to be optimized to handle pulling in the tags in a single query
        sql = "SELECT DISTINCT ON (pages.created_at, pages.id) pages.id, pages.uri, pages.average_rating, pages.total_ratings, "
        sql << "users.login, annorations.title, annorations.comment, annorations.user_id "
        sql << "FROM pages INNER JOIN annorations ON pages.first_annoration_id = annorations.id "
        sql << "INNER JOIN users ON annorations.user_id = users.id "
        sql << "ORDER BY pages.created_at DESC, pages.id "
        sql << "LIMIT #{max_pages} "
        Page.find_by_sql(sql)
    end
    
    #########################################################
    # Retrieves pages tagged a certain way
    def self.pages_tagged(tags, max_pages = 20)
        sql = "SELECT DISTINCT ON (pages.average_rating, pages.id) pages.id, pages.uri, pages.average_rating, pages.total_ratings, "
        sql << "users.login, annorations.title, annorations.comment, annorations.user_id "
        sql << "FROM pages INNER JOIN annorations ON pages.first_annoration_id = annorations.id "
        sql << "INNER JOIN users ON annorations.user_id = users.id "
        sql << "INNER JOIN pages_tags ON pages.id = pages_tags.page_id "
        sql << "INNER JOIN tags ON pages_tags.tag_id = tags.id "
        sql << "WHERE tags.name = ? "
        sql << "ORDER BY pages.average_rating DESC, pages.id "
        sql << "LIMIT #{max_pages} "
        Page.find_by_sql([sql, tags])
    end
    
    #########################################################
    # Retrieves alpha ordered list of top tags for this page
    def top_tags
        sql = "SELECT DISTINCT ON (tags.name, tags.id) * "
        sql << "FROM tags "
        sql << "INNER JOIN pages_tags ON tags.id = pages_tags.tag_id "
        sql << "WHERE (pages_tags.page_id = #{self.id}) "
        sql << "ORDER BY tags.name, tags.id "
        Tag.find_by_sql(sql)
    end

    #########################################################
    # Retrieves alpha ordered list of top tags for this page
    def self.top_tags(options = {:limit => 200})
        sql = "SELECT name, count FROM ( "
        sql << "SELECT DISTINCT ON (count, tags.name, tags.id) tags.name, COUNT(tags.id) AS count "
        sql << "FROM tags "
        sql << "INNER JOIN pages_tags ON tags.id = pages_tags.tag_id "
        sql << "GROUP BY tags.name, tags.id " 
        sql << "ORDER BY count DESC, tags.name, tags.id "
        add_limit!(sql, options)
        sql << ") AS t ORDER BY LOWER(t.name)"
        Tag.find_by_sql([sql,self.id])
    end
end
